﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VswService_V7.ClientCommand;
using VswService_V7.Utility;
using Oracle.ManagedDataAccess.Client;
using System.Reflection;
using System.Globalization;
using VswService_V7.DataBase.Tables.L2INTERFACE;

namespace VswService_V7.DataBase.Tables
{
    public partial class STRIP_INFO
    {
        #region field  
        public static readonly string tableName = "STRIP_INFO";
        public static readonly string fd_eventNo = "EVENT_NO";
        public static readonly string fd_stripNo = "STRIP_NO";
        public static readonly string fd_batchNoE = "BATCH_NO_E";
        public static readonly string fd_batchNoO = "BATCH_NO_O";
        public static readonly string fd_taskNo = "TASK_NO";
        public static readonly string fd_isSpy = "IS_SPY";
        public static readonly string fd_isEx = "IS_EX";
        public static readonly string fd_stripDurabilityClass = "STRIP_DURABILITY_CLASS";
        public static readonly string fd_stripSteelGrade = "STRIP_STEEL_GRADE";
        public static readonly string fd_stripHeat = "STRIP_HEAT";
        public static readonly string fd_stripThk = "STRIP_THK";
        public static readonly string fd_stripWidth = "STRIP_WIDTH";
        public static readonly string fd_stripWeight = "STRIP_WEIGHT";
        public static readonly string fd_burrDirection = "BURR_DIRECTION";
        public static readonly string fd_status = "STATUS";
        public static readonly string fd_updateTime = "UPDATE_TIME";
        #endregion


        #region view

        public static readonly string vw_coilsWatiingLoad = "VIEW_COILS_WAITINGLOAD";

        #endregion

        /// <summary>
        /// Query strip information in stripeList, where strip status=0;
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable SelectLoadStripInfo(List<string> list)
        {
            try
            {
                DataTable loadStripTable = CreateDataTable();
                for (int i = 0; i < list.Count; i++)
                {
                    string sql = string.Format("select * from {0} where {1}='{2}' and {3}='{4}'",
                        tableName, fd_stripNo, list[i], fd_status, "0");
                    DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
                    if (dataTable != null || dataTable.Rows.Count > 0)
                    {
                        DataRow dr = loadStripTable.NewRow();
                        dr[fd_eventNo] = dataTable.Rows[0][fd_eventNo];
                        dr[fd_stripNo] = dataTable.Rows[0][fd_stripNo];
                        dr[fd_batchNoE] = dataTable.Rows[0][fd_batchNoE];
                        dr[fd_batchNoO] = dataTable.Rows[0][fd_batchNoO];
                        dr[fd_taskNo] = dataTable.Rows[0][fd_taskNo];

                        dr[fd_isSpy] = dataTable.Rows[0][fd_isSpy];
                        dr[fd_isEx] = dataTable.Rows[0][fd_isEx];
                        dr[fd_stripDurabilityClass] = dataTable.Rows[0][fd_stripDurabilityClass];
                        dr[fd_stripSteelGrade] = dataTable.Rows[0][fd_stripSteelGrade];
                        dr[fd_stripHeat] = dataTable.Rows[0][fd_stripHeat];

                        dr[fd_stripThk] = dataTable.Rows[0][fd_stripThk];
                        dr[fd_stripWidth] = dataTable.Rows[0][fd_stripWidth];
                        dr[fd_stripWeight] = dataTable.Rows[0][fd_stripWeight];
                        dr[fd_burrDirection] = dataTable.Rows[0][fd_burrDirection];
                        dr[fd_status] = dataTable.Rows[0][fd_status];

                        dr[fd_updateTime] = dataTable.Rows[0][fd_updateTime];

                        loadStripTable.Rows.Add(dr);
                    }
                    else
                    {
                        DataRow dr = loadStripTable.NewRow();
                        dr[fd_eventNo] = -1;
                        dr[fd_stripNo] = "-1";
                        dr[fd_batchNoE] = "-1";
                        dr[fd_batchNoO] = "-1";
                        dr[fd_taskNo] = "-1";

                        dr[fd_isSpy] = "-1";
                        dr[fd_isEx] = "-1";
                        dr[fd_stripDurabilityClass] = "-1";
                        dr[fd_stripSteelGrade] = "-1";
                        dr[fd_stripHeat] = "-1";

                        dr[fd_stripThk] = "-1";
                        dr[fd_stripWidth] = "-1";
                        dr[fd_stripWeight] = "-1";
                        dr[fd_burrDirection] = "-1";
                        dr[fd_status] = "-1";

                        dr[fd_updateTime] = "-1";
                        loadStripTable.Rows.Add(dr);
                    }

                }
                return loadStripTable;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectLoadStripInfo", ex);
                throw ex;
            }
        }


        /// <summary>
        /// Query strip information in stripeList, where strip status=0 or 1;
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable SelectScanedStripInfo(List<string> list)
        {

            try
            {
                DataTable loadStripTable = CreateDataTable();
                for (int i = 0; i < list.Count; i++)
                {
                    string sql = string.Format("select * from {0} where {1}='{2}'",
                        tableName, fd_stripNo, list[i]);
                    DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
                    if (dataTable != null && dataTable.Rows.Count == 1)
                    {
                        if (dataTable.Rows[0][fd_status].ToString() == "0" || dataTable.Rows[0][fd_status].ToString() == "1")
                        {
                            DataRow dr = loadStripTable.NewRow();
                            dr[fd_eventNo] = dataTable.Rows[0][fd_eventNo];
                            dr[fd_stripNo] = dataTable.Rows[0][fd_stripNo];
                            dr[fd_batchNoE] = dataTable.Rows[0][fd_batchNoE];
                            dr[fd_batchNoO] = dataTable.Rows[0][fd_batchNoO];
                            dr[fd_taskNo] = dataTable.Rows[0][fd_taskNo];

                            dr[fd_isSpy] = dataTable.Rows[0][fd_isSpy];
                            dr[fd_isEx] = dataTable.Rows[0][fd_isEx];
                            dr[fd_stripDurabilityClass] = dataTable.Rows[0][fd_stripDurabilityClass];
                            dr[fd_stripSteelGrade] = dataTable.Rows[0][fd_stripSteelGrade];
                            dr[fd_stripHeat] = dataTable.Rows[0][fd_stripHeat];

                            dr[fd_stripThk] = dataTable.Rows[0][fd_stripThk];
                            dr[fd_stripWidth] = dataTable.Rows[0][fd_stripWidth];
                            dr[fd_stripWeight] = dataTable.Rows[0][fd_stripWeight];
                            dr[fd_burrDirection] = dataTable.Rows[0][fd_burrDirection];
                            dr[fd_status] = dataTable.Rows[0][fd_status];

                            dr[fd_updateTime] = dataTable.Rows[0][fd_updateTime];

                            loadStripTable.Rows.Add(dr);
                        }
                        else
                        {
                            DataRow dr = loadStripTable.NewRow();
                            dr[fd_eventNo] = -1;
                            dr[fd_stripNo] = "-1";
                            dr[fd_batchNoE] = "-1";
                            dr[fd_batchNoO] = "-1";
                            dr[fd_taskNo] = "-1";

                            dr[fd_isSpy] = "-1";
                            dr[fd_isEx] = "-1";
                            dr[fd_stripDurabilityClass] = "-1";
                            dr[fd_stripSteelGrade] = "-1";
                            dr[fd_stripHeat] = "-1";

                            dr[fd_stripThk] = "-1";
                            dr[fd_stripWidth] = "-1";
                            dr[fd_stripWeight] = "-1";
                            dr[fd_burrDirection] = "-1";
                            dr[fd_status] = "-1";

                            dr[fd_updateTime] = "-1";
                            loadStripTable.Rows.Add(dr);
                        }

                    }
                    else
                    {
                        DataRow dr = loadStripTable.NewRow();
                        dr[fd_eventNo] = -1;
                        dr[fd_stripNo] = "-1";
                        dr[fd_batchNoE] = "-1";
                        dr[fd_batchNoO] = "-1";
                        dr[fd_taskNo] = "-1";

                        dr[fd_isSpy] = "-1";
                        dr[fd_isEx] = "-1";
                        dr[fd_stripDurabilityClass] = "-1";
                        dr[fd_stripSteelGrade] = "-1";
                        dr[fd_stripHeat] = "-1";

                        dr[fd_stripThk] = "-1";
                        dr[fd_stripWidth] = "-1";
                        dr[fd_stripWeight] = "-1";
                        dr[fd_burrDirection] = "-1";
                        dr[fd_status] = "-1";

                        dr[fd_updateTime] = "-1";
                        loadStripTable.Rows.Add(dr);
                    }

                }
                return loadStripTable;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectLoadStripInfo", ex);
                throw ex;
            }
        }


        /// <summary>
        /// Query strip information in stripeList, where strip status=0 or 1;
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable CheckScanedStripInfo(List<string> list,out int scanedCount)
        {
            try
            {
                scanedCount = 0;
                DataTable loadStripTable = CreateDataTable();
                for (int i = 0; i < list.Count; i++)
                {
                    string sql = string.Format("select * from {0} where {1}='{2}'",
                        tableName, fd_stripNo, list[i]);
                    DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
                    if (dataTable != null && dataTable.Rows.Count == 1)
                    {
                        if (dataTable.Rows[0][fd_status].ToString() == "0" || dataTable.Rows[0][fd_status].ToString() == "1")
                        {
                            DataRow dr = loadStripTable.NewRow();
                            dr[fd_eventNo] = dataTable.Rows[0][fd_eventNo];
                            dr[fd_stripNo] = dataTable.Rows[0][fd_stripNo];
                            dr[fd_batchNoE] = dataTable.Rows[0][fd_batchNoE];
                            dr[fd_batchNoO] = dataTable.Rows[0][fd_batchNoO];
                            dr[fd_taskNo] = dataTable.Rows[0][fd_taskNo];

                            dr[fd_isSpy] = dataTable.Rows[0][fd_isSpy];
                            dr[fd_isEx] = dataTable.Rows[0][fd_isEx];
                            dr[fd_stripDurabilityClass] = dataTable.Rows[0][fd_stripDurabilityClass];
                            dr[fd_stripSteelGrade] = dataTable.Rows[0][fd_stripSteelGrade];
                            dr[fd_stripHeat] = dataTable.Rows[0][fd_stripHeat];

                            dr[fd_stripThk] = dataTable.Rows[0][fd_stripThk];
                            dr[fd_stripWidth] = dataTable.Rows[0][fd_stripWidth];
                            dr[fd_stripWeight] = dataTable.Rows[0][fd_stripWeight];
                            dr[fd_burrDirection] = dataTable.Rows[0][fd_burrDirection];
                            dr[fd_status] = dataTable.Rows[0][fd_status];

                            dr[fd_updateTime] = dataTable.Rows[0][fd_updateTime];

                            loadStripTable.Rows.Add(dr);

                            if(dataTable.Rows[0][fd_status].ToString() == "1"&& !string.IsNullOrEmpty(dataTable.Rows[0][fd_batchNoO].ToString()) && dataTable.Rows[0][fd_batchNoO].ToString().Length > 5 &&
                                !string.IsNullOrEmpty(dataTable.Rows[0][fd_batchNoE].ToString()) && dataTable.Rows[0][fd_batchNoE].ToString().Length > 5)
                            {
                                scanedCount += 1;
                            }
                            
                        }
                        else
                        {
                            DataRow dr = loadStripTable.NewRow();
                            dr[fd_eventNo] = -1;
                            dr[fd_stripNo] = list[i];
                            dr[fd_batchNoE] = "-1";
                            dr[fd_batchNoO] = "-1";
                            dr[fd_taskNo] = "-1";

                            dr[fd_isSpy] = "-1";
                            dr[fd_isEx] = "-1";
                            dr[fd_stripDurabilityClass] = "-1";
                            dr[fd_stripSteelGrade] = "-1";
                            dr[fd_stripHeat] = "-1";

                            dr[fd_stripThk] = "-1";
                            dr[fd_stripWidth] = "-1";
                            dr[fd_stripWeight] = "-1";
                            dr[fd_burrDirection] = "-1";
                            dr[fd_status] = dataTable.Rows[0][fd_status];

                            dr[fd_updateTime] = "-1";
                            loadStripTable.Rows.Add(dr);
                        }

                    }
                    else
                    {
                        DataRow dr = loadStripTable.NewRow();
                        dr[fd_eventNo] = -1;
                        dr[fd_stripNo] = list[i];
                        dr[fd_batchNoE] = "-1";
                        dr[fd_batchNoO] = "-1";
                        dr[fd_taskNo] = "-1";

                        dr[fd_isSpy] = "-1";
                        dr[fd_isEx] = "-1";
                        dr[fd_stripDurabilityClass] = "-1";
                        dr[fd_stripSteelGrade] = "-1";
                        dr[fd_stripHeat] = "-1";

                        dr[fd_stripThk] = "-1";
                        dr[fd_stripWidth] = "-1";
                        dr[fd_stripWeight] = "-1";
                        dr[fd_burrDirection] = "-1";
                        dr[fd_status] = "-1";

                        dr[fd_updateTime] = "-1";
                        loadStripTable.Rows.Add(dr);
                    }

                }
                return loadStripTable;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("CheckScanedStripInfo", ex);
                throw ex;
            }
        }


        /// <summary>
        /// use previous stripInfo to select again in database after 12 seconds delay.
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public static int CheckRestScanedStripInfo(DataTable dataTable)
        {
            try
            {
                int faildCount = 0;
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    if (!string.IsNullOrEmpty(dataTable.Rows[i][fd_stripNo].ToString()) && dataTable.Rows[i][fd_stripNo].ToString().Length > 5 &&
                            (dataTable.Rows[i][fd_status].ToString() == "0" || (dataTable.Rows[i][fd_status].ToString() == "1" &&
                            (string.IsNullOrEmpty(dataTable.Rows[i][fd_batchNoO].ToString()) || dataTable.Rows[i][fd_batchNoO].ToString().Length <= 5 ||
                            string.IsNullOrEmpty(dataTable.Rows[i][fd_batchNoE].ToString()) || dataTable.Rows[i][fd_batchNoE].ToString().Length <= 5))))
                    {

                        string sql = string.Format("select * from {0} where {1}='{2}'",
                            tableName, fd_stripNo, dataTable.Rows[i][fd_stripNo].ToString());
                        DataTable resultTable = OracleHelper.Exct_QueryTable(sql);
                        if (resultTable != null && resultTable.Rows.Count == 1)
                        {
                            if (resultTable.Rows[0][fd_status].ToString() == "0" || resultTable.Rows[0][fd_status].ToString() == "1")
                            {
                                dataTable.Rows[i][fd_eventNo] = resultTable.Rows[0][fd_eventNo];
                                dataTable.Rows[i][fd_stripNo] = resultTable.Rows[0][fd_stripNo];
                                dataTable.Rows[i][fd_batchNoE] = resultTable.Rows[0][fd_batchNoE];
                                dataTable.Rows[i][fd_batchNoO] = resultTable.Rows[0][fd_batchNoO];
                                dataTable.Rows[i][fd_taskNo] = resultTable.Rows[0][fd_taskNo];

                                dataTable.Rows[i][fd_isSpy] = resultTable.Rows[0][fd_isSpy];
                                dataTable.Rows[i][fd_isEx] = resultTable.Rows[0][fd_isEx];
                                dataTable.Rows[i][fd_stripDurabilityClass] = resultTable.Rows[0][fd_stripDurabilityClass];
                                dataTable.Rows[i][fd_stripSteelGrade] = resultTable.Rows[0][fd_stripSteelGrade];
                                dataTable.Rows[i][fd_stripHeat] = resultTable.Rows[0][fd_stripHeat];

                                dataTable.Rows[i][fd_stripThk] = resultTable.Rows[0][fd_stripThk];
                                dataTable.Rows[i][fd_stripWidth] = resultTable.Rows[0][fd_stripWidth];
                                dataTable.Rows[i][fd_stripWeight] = resultTable.Rows[0][fd_stripWeight];
                                dataTable.Rows[i][fd_burrDirection] = resultTable.Rows[0][fd_burrDirection];
                                dataTable.Rows[i][fd_status] = resultTable.Rows[0][fd_status];

                                dataTable.Rows[i][fd_updateTime] = resultTable.Rows[0][fd_updateTime];
                            }
                            else
                            {
                                faildCount += 1;
                            }

                        }
                        else
                        {
                            faildCount += 1;
                        }

                    }
                    if (string.IsNullOrEmpty(dataTable.Rows[i][fd_stripNo].ToString()) || dataTable.Rows[i][fd_stripNo].ToString().Length <= 5)
                    {
                        faildCount += 1;
                    }
                    if (dataTable.Rows[i][fd_status].ToString() == "-1")
                    {
                        faildCount += 1;
                    }

                }
                return faildCount;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("CheckRestScanedStripInfo", ex);
                throw ex;
            }
        }


        /// <summary>
        /// L2 requests batchNo from L3
        /// </summary>
        /// <param name="list"></param>
        public static void RequestBatchNo(List<string> list)
        {
            try
            {
                DataTable stripTable = CreateDataTable();
                for (int i = 0; i < list.Count; i++)
                {
                    string sql = string.Format("select * from {0} where {1}='{2}' and {3}='{4}'",
                        tableName, fd_stripNo, list[i], fd_status, "0");
                    DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
                    if (dataTable != null && dataTable.Rows.Count > 0)
                    {
                        long eventNo = OracleHelper.GetNextEventNo();
                        string taskNo = dataTable.Rows[0][fd_taskNo].ToString();
                        string stripNo = dataTable.Rows[0][fd_stripNo].ToString();
                        string seq = (i + 1).ToString();
                        if (dataTable.Rows[0][fd_batchNoE].ToString() != "" && dataTable.Rows[0][fd_batchNoE].ToString() != "0" && dataTable.Rows[0][fd_batchNoE] != null)
                        {
                            if (dataTable.Rows[0][fd_batchNoO].ToString() != "" && dataTable.Rows[0][fd_batchNoO].ToString() != "0" && dataTable.Rows[0][fd_batchNoO] != null)
                            {
                                L2INTF_BATCH.InsertIntfBatch(eventNo, taskNo, stripNo, seq);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("RequestBatchNo", ex);
                throw ex;
            }
        }

        /// <summary>
        /// Determine if batchno is empty
        /// </summary>
        /// <param name="list"></param>
        public static bool IsNullBatchNo(List<string> list)
        {
            try
            {
                bool result = false;
                DataTable stripTable = CreateDataTable();
                for (int i = 0; i < list.Count; i++)
                {
                    string sql = string.Format("select * from {0} where {1}='{2}' and ({3}='{4}' or {5}='{6}')",
                        tableName, fd_stripNo, list[i], fd_status, scaned,fd_status, created);
                    DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
                    if (dataTable != null && dataTable.Rows.Count > 0)
                    {
                        if (dataTable.Rows[0][fd_batchNoE] == null || dataTable.Rows[0][fd_batchNoE].ToString() == "0" || dataTable.Rows[0][fd_batchNoE].ToString() == ""||
                            dataTable.Rows[0][fd_batchNoO] == null || dataTable.Rows[0][fd_batchNoO].ToString() == "0" || dataTable.Rows[0][fd_batchNoO].ToString() == "")
                        {
                            result = true;
                            break;
                        }
                    }
                    else
                    {
                        result = true;
                        break;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("RequestBatchNo", ex);
                throw ex;
            }
        }

        /// <summary>
        ///Strip Scan Change state 0 to state 1
        /// </summary>
        /// <param name="stripTable"></param>
        public static void UpdateStripStatusAfterScaned(DataTable stripTable)
        {
            try
            {
                if (stripTable.Rows.Count > 0)
                {
                    List<string> sqlList = new List<string>();
                    long eventNo = OracleHelper.GetNextEventNo();
                    for (int i = 0; i < stripTable.Rows.Count; i++)
                    {
                        if (stripTable.Rows[i][fd_stripNo].ToString() != "-1" && stripTable.Rows[i][fd_status].ToString() == "0")
                        {
                            string sql = string.Format("update {0} set {1}='{2}' where {3}='{4}'", tableName, fd_status, scaned, fd_stripNo, stripTable.Rows[i][fd_stripNo].ToString());
                            string sqlINTFSTRIP = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')",
                    L2INTF_STRIP_INFO.tableName, eventNo, stripTable.Rows[i][fd_stripNo].ToString(), stripTable.Rows[i][fd_batchNoE].ToString(), stripTable.Rows[i][fd_taskNo].ToString(),
                    stripTable.Rows[i][fd_isSpy].ToString(), stripTable.Rows[i][fd_isEx].ToString(), stripTable.Rows[i][fd_stripDurabilityClass].ToString(), stripTable.Rows[i][fd_stripSteelGrade].ToString(),
                    stripTable.Rows[i][fd_stripHeat].ToString(), stripTable.Rows[i][fd_stripThk].ToString(), stripTable.Rows[i][fd_stripWidth].ToString(), stripTable.Rows[i][fd_stripWeight].ToString(),
                    stripTable.Rows[i][fd_burrDirection].ToString(), scaned, DateTime.Now.ToString(Formats.GetDateFormat()));
                            sqlList.Add(sql);
                            sqlList.Add(sqlINTFSTRIP);
                        }
                        string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                            L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.updateStripStatus, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");

                        sqlList.Add(sqlINTFEVENT);
                    }

                    if (sqlList.Count > 0)
                    {
                        OracleHelper.ExecuteSqlTran(sqlList);
                    }

                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdateStripStatusAfterScaned", ex);
                throw ex;
            }
        }

        /// <summary>
        /// Create a list of sql statements that update stripNo status1 => 2 and execute the transaction
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static bool UpdateAvailableStripStatus(List<string> list)
        {
            try
            {
                if (list.Count > 0)
                {
                    List<string> sqlList = new List<string>();
                    for (int i = 0; i < list.Count; i++)
                    {
                        DataTable stripTable = OracleHelper.Exct_QueryTable(string.Format("select c.*,d.{0} as {1} from {2} d,(select * from {3} where {4} in (select a.{5} from {6} a,{7} b where a.{8}=b.{9} and b.{10}='{11}')) c where c.{12}=d.{13}",
                            TASK_INFO.fd_status, "TASK_STATUS", TASK_INFO.tableName, tableName, fd_taskNo, TASK_INFO.fd_taskNo, TASK_INFO.tableName, tableName, TASK_INFO.fd_taskNo, fd_taskNo, fd_stripNo, list[i], fd_taskNo, TASK_INFO.fd_taskNo));
                        string taskStatus = stripTable.Rows[i]["TASK_STATUS"].ToString();
                        if (taskStatus == TASK_INFO.createdNotBinded)
                        {
                            return false;
                        }
                        long eventNo = OracleHelper.GetNextEventNo();
                        string sql = string.Format("update {0} set {1}='{2}' where {3}='{4}'", tableName, fd_status, Loaded, fd_stripNo, list[i]);
                        string sqlINTFSTRIP = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')",
                    L2INTF_STRIP_INFO.tableName, eventNo, stripTable.Rows[0][fd_stripNo].ToString(), stripTable.Rows[0][fd_batchNoE].ToString(), stripTable.Rows[0][fd_taskNo].ToString(),
                    stripTable.Rows[0][fd_isSpy].ToString(), stripTable.Rows[0][fd_isEx].ToString(), stripTable.Rows[0][fd_stripDurabilityClass].ToString(), stripTable.Rows[0][fd_stripSteelGrade].ToString(),
                    stripTable.Rows[0][fd_stripHeat].ToString(), stripTable.Rows[0][fd_stripThk].ToString(), stripTable.Rows[0][fd_stripWidth].ToString(), stripTable.Rows[0][fd_stripWeight].ToString(),
                    stripTable.Rows[0][fd_burrDirection].ToString(), Loaded, DateTime.Now.ToString(Formats.GetDateFormat()));
                        string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                            L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.updateStripStatus, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");
                        sqlList.Add(sql);
                        sqlList.Add(sqlINTFSTRIP);
                        sqlList.Add(sqlINTFEVENT);
                    }
                    OracleHelper.ExecuteSqlTran(sqlList);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdateAvailableStripStatus", ex);
                throw ex;
            }
        }

        /// <summary>
        /// Create a list of sql statements that update stripNo status1 => 2 and execute the transaction
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static bool UpdateStripStatusAfterLoad(List<string> list)
        {
            try
            {
                if (list.Count > 0)
                {
                    DataTable stripTaskNoTable = OracleHelper.Exct_QueryTable(string.Format("select {0} from {1} where {2}='{3}'",
                            fd_taskNo, tableName, fd_stripNo, list[0]));

                    if (stripTaskNoTable.Rows.Count == 1)
                    {
                        DataTable taskTable = OracleHelper.Exct_QueryTable(string.Format("select {0} from {1} where {2}='{3}'",
                            TASK_INFO.fd_status, TASK_INFO.tableName, TASK_INFO.fd_taskNo, stripTaskNoTable.Rows[0][fd_taskNo]));


                        if (taskTable.Rows.Count == 1)
                        {
                            if (taskTable.Rows[0][TASK_INFO.fd_status].ToString() == TASK_INFO.createdNotBinded)
                            {
                                return false;
                            }
                        }
                        else
                        {
                            return false;
                        }
                    }
                    else
                    {
                        return false;
                    }


                    List<string> sqlList = new List<string>();
                    for (int i = 0; i < list.Count; i++)
                    {

                        DataTable stripTable = OracleHelper.Exct_QueryTable(string.Format("select * from {0} where {1}='{2}'",
                            tableName, fd_stripNo, list[i]));

                        long eventNo = OracleHelper.GetNextEventNo();
                        string sql = string.Format("update {0} set {1}='{2}' where {3}='{4}'", tableName, fd_status, Loaded, fd_stripNo, list[i]);
                        string sqlINTFSTRIP = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')",
                    L2INTF_STRIP_INFO.tableName, eventNo, stripTable.Rows[0][fd_stripNo].ToString(), stripTable.Rows[0][fd_batchNoO].ToString(), stripTable.Rows[0][fd_taskNo].ToString(),
                    stripTable.Rows[0][fd_isSpy].ToString(), stripTable.Rows[0][fd_isEx].ToString(), stripTable.Rows[0][fd_stripDurabilityClass].ToString(), stripTable.Rows[0][fd_stripSteelGrade].ToString(),
                    stripTable.Rows[0][fd_stripHeat].ToString(), stripTable.Rows[0][fd_stripThk].ToString(), stripTable.Rows[0][fd_stripWidth].ToString(), stripTable.Rows[0][fd_stripWeight].ToString(),
                    stripTable.Rows[0][fd_burrDirection].ToString(), Loaded, DateTime.Now.ToString(Formats.GetDateFormat()));
                        string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                            L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.updateStripStatus, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");
                        sqlList.Add(sql);
                        sqlList.Add(sqlINTFSTRIP);
                        sqlList.Add(sqlINTFEVENT);
                    }
                    OracleHelper.ExecuteSqlTran(sqlList);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdateAvailableStripStatus", ex);
                throw ex;
            }
        }


        /// <summary>
        /// update strip status to uncoil and inform L3
        /// </summary>
        /// <param name="stripNo"></param>
        /// <returns></returns>
        public static bool UpdateStripStatusAfterUncoil(string stripNo)
        {
            try
            {
                List<string> sqlList = new List<string>();
                DataTable stripTable= OracleHelper.Exct_QueryTable(string.Format("select * from {0} where {1}='{2}'",
                            tableName, fd_stripNo, stripNo));
                if (stripTable != null && stripTable.Rows.Count >0)
                {
                    //已经是UNCOIL的就不要再改变了
                    if(stripTable.Rows[0][fd_status].ToString()!=uncoiled)
                    {
                        long eventNo = OracleHelper.GetNextEventNo();
                        string sql = string.Format("update {0} set {1}='{2}' where {3}='{4}'", tableName, fd_status, uncoiled, fd_stripNo, stripNo);
                        string sqlINTFSTRIP = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')",
                    L2INTF_STRIP_INFO.tableName, eventNo, stripTable.Rows[0][fd_stripNo].ToString(), stripTable.Rows[0][fd_batchNoO].ToString(), stripTable.Rows[0][fd_taskNo].ToString(),
                    stripTable.Rows[0][fd_isSpy].ToString(), stripTable.Rows[0][fd_isEx].ToString(), stripTable.Rows[0][fd_stripDurabilityClass].ToString(), stripTable.Rows[0][fd_stripSteelGrade].ToString(),
                    stripTable.Rows[0][fd_stripHeat].ToString(), stripTable.Rows[0][fd_stripThk].ToString(), stripTable.Rows[0][fd_stripWidth].ToString(), stripTable.Rows[0][fd_stripWeight].ToString(),
                    stripTable.Rows[0][fd_burrDirection].ToString(), Loaded, DateTime.Now.ToString(Formats.GetDateFormat()));
                        string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                            L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.updateStripStatus, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");
                        sqlList.Add(sql);
                        sqlList.Add(sqlINTFSTRIP);
                        sqlList.Add(sqlINTFEVENT);
                        OracleHelper.ExecuteSqlTran(sqlList);
                        
                    }

                    return true;

                }
                else
                {
                    return false;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdateStripStatusAfterUncoil", ex);
                throw ex;
            }
        }

        /// <summary>
        /// The last tube of FCO will strip status2 => 3
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static void UpdateStripStatusAfterFCO(string stripNo)
        {
            try
            {
                List<string> sqlList = new List<string>();
                DataTable stripTable = OracleHelper.Exct_QueryTable(string.Format("select * from {0} where {1}='{2}'", tableName, fd_stripNo, stripNo));

                if(stripTable!=null && stripTable.Rows.Count>0)
                {
                    long eventNo = OracleHelper.GetNextEventNo();
                    string sql = string.Format("update {0} set {1}='{2}' where {3}='{4}'", tableName, fd_status, finished, fd_stripNo, stripNo);
                    string sqlINTFSTRIP = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')",
                L2INTF_STRIP_INFO.tableName, eventNo, stripTable.Rows[0][fd_stripNo].ToString(), stripTable.Rows[0][fd_batchNoE].ToString(), stripTable.Rows[0][fd_taskNo].ToString(),
                stripTable.Rows[0][fd_isSpy].ToString(), stripTable.Rows[0][fd_isEx].ToString(), stripTable.Rows[0][fd_stripDurabilityClass].ToString(), stripTable.Rows[0][fd_stripSteelGrade].ToString(),
                stripTable.Rows[0][fd_stripHeat].ToString(), stripTable.Rows[0][fd_stripThk].ToString(), stripTable.Rows[0][fd_stripWidth].ToString(), stripTable.Rows[0][fd_stripWeight].ToString(),
                stripTable.Rows[0][fd_burrDirection].ToString(), finished, DateTime.Now.ToString(Formats.GetDateFormat()));
                    string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                        L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.updateStripStatus, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");
                    sqlList.Add(sql);
                    sqlList.Add(sqlINTFSTRIP);
                    sqlList.Add(sqlINTFEVENT);
                    OracleHelper.ExecuteSqlTran(sqlList);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdateStripStatusAfterFCO", ex);
            }
        }

        /// <summary>
        /// Strip Unload Change state 2 to state 1
        /// </summary>
        /// <param name="stripNo"></param>
        /// <returns></returns>
        public static bool UnloadStripNo(string stripNo)
        {
            try
            {
                List<string> list = new List<string>();
                DataTable stripTable = OracleHelper.Exct_QueryTable(string.Format("select * from {0} where {1}='{2}' and {3}='{4}'", tableName, fd_stripNo, stripNo,fd_status,Loaded));

                if(stripTable!=null && stripTable.Rows.Count>0)
                {
                    long eventNo = OracleHelper.GetNextEventNo();
                    string sql = string.Format("update {0} " +
                        "set {1}='{2}' " +
                        "where {3}='{4}' and {5}='{6}'",
                        tableName,
                        fd_status, scaned,
                        fd_stripNo, stripNo, fd_status, Loaded);
                    string sqlINTFSTRIP = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')",
                        L2INTF_STRIP_INFO.tableName, eventNo, stripTable.Rows[0][fd_stripNo].ToString(), stripTable.Rows[0][fd_batchNoE].ToString(), stripTable.Rows[0][fd_taskNo].ToString(),
                        stripTable.Rows[0][fd_isSpy].ToString(), stripTable.Rows[0][fd_isEx].ToString(), stripTable.Rows[0][fd_stripDurabilityClass].ToString(), stripTable.Rows[0][fd_stripSteelGrade].ToString(),
                        stripTable.Rows[0][fd_stripHeat].ToString(), stripTable.Rows[0][fd_stripThk].ToString(), stripTable.Rows[0][fd_stripWidth].ToString(), stripTable.Rows[0][fd_stripWeight].ToString(),
                        stripTable.Rows[0][fd_burrDirection].ToString(), scaned, DateTime.Now.ToString(Formats.GetDateFormat()));
                    string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                        L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.updateStripStatus, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");
                    list.Add(sql);
                    list.Add(sqlINTFSTRIP);
                    list.Add(sqlINTFEVENT);
                    OracleHelper.ExecuteSqlTran(list);
                    return true;
                }
                else
                {
                    return false;
                }
                
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UnloadStripNo", ex);
                return false;
                throw ex;
            }
        }

        /// <summary>
        /// Query STRIP INFO according to STRIP NO and status = 2 (volume rolled)
        /// </summary>
        /// <returns></returns>
        public static DataTable SelectStripInfo(string stripNo)
        {
            try
            {
                string sql = string.Format("select * from {0} where {1}='{2}' and {3}='{4}'", tableName, fd_stripNo, stripNo, fd_status, uncoiled);
                DataTable dt = OracleHelper.Exct_QueryTable(sql);
                return dt;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectStripInfo", ex);
                throw ex;
            }
        }

        /// <summary>
        /// 查询开卷的钢卷信息
        /// </summary>
        /// <param name="stripNo"></param>
        /// <returns></returns>
        public static DataTable SelectUncoiledStripInfo(string stripNo)
        {
            try
            {
                //string sql = string.Format("select * from {0} where {1}='{2}' and {3}='{4}'", tableName, fd_stripNo, stripNo, fd_status, uncoiled);
                string sql = string.Format("select * from {0} where {1}='{2}'", tableName, fd_stripNo, stripNo);
                DataTable dt = OracleHelper.Exct_QueryTable(sql);
                return dt;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectStripInfo", ex);
                throw ex;
            }
        }

        /// <summary>
        /// Create strip info table
        /// </summary>
        /// <returns></returns>
        public static DataTable CreateDataTable()
        {

            DataTable dt = new DataTable("stripInfo_temp");
            DataColumn dc1 = new DataColumn(fd_eventNo, Type.GetType("System.Int64"));
            DataColumn dc2 = new DataColumn(fd_stripNo, Type.GetType("System.String"));
            DataColumn dc3 = new DataColumn(fd_batchNoE, Type.GetType("System.String"));
            DataColumn dc4 = new DataColumn(fd_batchNoO, Type.GetType("System.String"));
            DataColumn dc5 = new DataColumn(fd_taskNo, Type.GetType("System.String"));
            DataColumn dc6 = new DataColumn(fd_isSpy, Type.GetType("System.String"));
            DataColumn dc7 = new DataColumn(fd_isEx, Type.GetType("System.String"));
            DataColumn dc8 = new DataColumn(fd_stripDurabilityClass, Type.GetType("System.String"));
            DataColumn dc9 = new DataColumn(fd_stripSteelGrade, Type.GetType("System.String"));
            DataColumn dc10 = new DataColumn(fd_stripHeat, Type.GetType("System.String"));
            DataColumn dc11 = new DataColumn(fd_stripThk, Type.GetType("System.String"));
            DataColumn dc12 = new DataColumn(fd_stripWidth, Type.GetType("System.String"));
            DataColumn dc13 = new DataColumn(fd_stripWeight, Type.GetType("System.String"));
            DataColumn dc14 = new DataColumn(fd_burrDirection, Type.GetType("System.String"));
            DataColumn dc15 = new DataColumn(fd_status, Type.GetType("System.String"));
            DataColumn dc16 = new DataColumn(fd_updateTime, Type.GetType("System.String"));
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            dt.Columns.Add(dc5);

            dt.Columns.Add(dc6);
            dt.Columns.Add(dc7);
            dt.Columns.Add(dc8);
            dt.Columns.Add(dc9);
            dt.Columns.Add(dc10);

            dt.Columns.Add(dc11);
            dt.Columns.Add(dc12);
            dt.Columns.Add(dc13);
            dt.Columns.Add(dc14);
            dt.Columns.Add(dc15);

            dt.Columns.Add(dc16);

            return dt;
        }
    }


    public class StripInfo
    {
        public StripInfo()
        {
            eventNo = "-1";
            stripNo = "-1";
            batchNoE = "-1";
            batchNoO = "-1";
            taskNo = "-1";
            isSpy = "-1";
            isEx = "-1";
            stripDurabilityClass = "-1";
            stripSteelGrade = "-1";
            stripHeat = "-1";
            stripThk = "-1";
            stripWidth = "-1";
            stripWeight = "-1";
            burrDirection = "-1";
            status = "-1";
            updateTime = "-1";
        }

        public string eventNo;
        public string stripNo;
        public string batchNoE;
        public string batchNoO;
        public string taskNo;
        public string isSpy;
        public string isEx;
        public string stripDurabilityClass;
        public string stripSteelGrade;
        public string stripHeat;
        public string stripThk;
        public string stripWidth;
        public string stripWeight;
        public string burrDirection;
        public string status;
        public string updateTime;



    }


    public class StripInfoForLoadAsk
    {
        public StripInfoForLoadAsk()
        {
            batchNoA = "-1";
            taskNo = "-1";
            isSpy = "-1";
            stripDurabilityClass = "-1";
            stripThk = "-1";
            stripWidth = "-1";
            stripWeight = "-1";
            burrDirection = "-1";

        }


        public string batchNoA;
        public string taskNo;
        public string isSpy;
        public string stripDurabilityClass;
        public string stripThk;
        public string stripWidth;
        public string burrDirection;
        public string stripWeight;

    }


}
